﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_RemoveOldFilesAll]
#-- Purpose:		Calls spBkp_RemoveOldFiles for all databases.
#--	Last Update:	08/27/2014
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_RemoveOldFilesAll]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@db_name sysname
DECLARE		@default_bkp_dir nvarchar(4000), @sError nvarchar(4000), @recovery_window int
DECLARE		@tFiles TABLE (physical_device_name nvarchar(4000), last_write_date datetime)
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBkp_RemoveOldFilesAll'

--- Determine the configured Recovery Windows Days
SELECT		@recovery_window = ISNULL(cfg_recover_window_days, 8)
FROM		[dbo].[tblMaint_Config]

--- Default to 8 days if nothing is found in tblConfiguration
IF @recovery_window IS NULL
	SET @recovery_window = 8

-----------------------------------------------------------
---
--- Remove the backup files that are no longer needed to meet
---	the recovery window for current databases
---
-----------------------------------------------------------

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		db.name
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.database_id = mf.database_id
				AND mf.type = 0
	GROUP BY	db.name
	ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC [dbo].[spBkp_RemoveOldFiles] @database_name = @db_name

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

-----------------------------------------------------------
---
--- Build the list of files that no longer have a database
---
-----------------------------------------------------------

--- Get the default backup directory
EXEC		[dbo].[spBkp_GetDirectory]	@database_name = NULL, 
										@backup_directory_name = @default_bkp_dir OUTPUT

--- Get the list of files to delete from the file system
DECLARE crs CURSOR FOR
	SELECT		t1.subdirectory_name
	FROM		[dbo].[fnIO_GetSubdirectories] (@default_bkp_dir, 1) t1
	LEFT JOIN	[master].[sys].[databases] d ON REPLACE(t1.subdirectory_name, @default_bkp_dir, '') = d.name
	WHERE		d.name is null
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	INSERT INTO @tFiles
		SELECT physical_device_name, last_write_date FROM [dbo].[fnIO_GetFiles] (@default_bkp_dir, 1)
									
	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

--- Delete Files
DECLARE crs CURSOR FOR 
	SELECT		physical_device_name
	FROM		@tFiles
	WHERE		last_write_date < DATEADD(dd, (-1 * @recovery_window), GETDATE())
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Set the message and log it to the error log
	SELECT	@sError = 'Deleting backup file: ' + @default_bkp_dir
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

	BEGIN TRY
		--- Delete the backup file
		EXEC	[dbo].[spIO_DeleteFile]  @filePath = @default_bkp_dir
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Failed to delete backup file: ' + @default_bkp_dir
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError
	END CATCH

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

-----------------------------------------------------------
---
--- Build the list of directories that no longer have 
---	databases and delete them.
---
-----------------------------------------------------------

--- Get the default backup directory
EXEC		[dbo].[spBkp_GetDirectory]	@database_name = NULL, 
										@backup_directory_name = @default_bkp_dir OUTPUT
DECLARE crs CURSOR FOR
	SELECT		t1.subdirectory_name
	FROM		[dbo].[fnIO_GetSubdirectories] (@default_bkp_dir, 1) t1
	LEFT JOIN	[master].[sys].[databases] d ON REPLACE(t1.subdirectory_name, @default_bkp_dir, '') = d.name
	WHERE		d.name is null
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF NOT EXISTS (SELECT TOP 1 physical_device_name FROM [dbo].[fnIO_GetFiles] (@default_bkp_dir, 1))
	  BEGIN
		--- Set the message and log it to the error log
		SELECT	@sError = 'Deleting subdirectory of old database: ' + @default_bkp_dir
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

		BEGIN TRY
			--- Delete the backup file
			EXEC	[dbo].[spIO_DeleteDirectory] @directoryPath = @default_bkp_dir, @recursive = 1
		END TRY
		BEGIN CATCH
			SELECT	@sError = 'Failed to delete subdirectory of old database: ' + @default_bkp_dir
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError
		END CATCH
	  END

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF